import 'dart:io';

import 'package:flutter/material.dart';
import 'package:flutter_mode/utils.dart';
import 'package:flutter_mode/zyj/ui/webview.dart';
import 'package:fluttertoast/fluttertoast.dart';
import 'package:sqflite/sqflite.dart';

class SqlLiteDemo extends StatelessWidget {
  Future<String> _databasePath() async {
    var databasesPath = await getDatabasesPath();
    return databasesPath + '/sqldemo.db';
  }

  Future<Database> _database() async {
    var path = await _databasePath();
    var database = await openDatabase(path, version: 1);

    return database;
  }

  @override
  Widget build(BuildContext context) {
    // TODO: https://pub.flutter-io.cn/packages/sqflite#-readme-tab-
    var items = {
      '创建数据库': () {
        _database().then((db) => db.close());
        Fluttertoast.showToast(msg: "创建数据库完成");
      },
      '删除数据库': () async {
        deleteDatabase(await _databasePath());
      },
      '检测数据库是否存在': () async {
        final file = File(await _databasePath());
        if (await file.exists()) {
          Fluttertoast.showToast(msg: "数据库已存在");
        } else {
          Fluttertoast.showToast(msg: "数据库不存在");
        }
      },
      '  ': () {},
      '创建表': () async {
        var database = await _database();
        database.execute(
            'create table Demo (id integer primary key, name text, age integer)');
        Fluttertoast.showToast(msg: "创建表完成");

        database.close();
      },
      '删除表': () async {
        var database = await _database();

        await database.execute('drop table Demo');

        Fluttertoast.showToast(msg: "删除表完成");

        database.close();
      },
      '检测表是否存在,通过异常抓取': () async {
        var database = await _database();
        try {
          var datas = await database.rawQuery('select * from Demo limit 0');
          print(datas);
          Fluttertoast.showToast(msg: "表存在");
        } on DatabaseException catch (e) {
          if (e.isNoSuchTableError()) {
            Fluttertoast.showToast(msg: "表不存在");
          } else {
            Fluttertoast.showToast(msg: "表存在, 但是有其他异常$e");
          }
        } on Exception catch (s) {
          print(s);
        } catch (e, s) {}

        database.close();
      },
      '    ': () {},
      '添加数据': () async {
        var database = await _database();

        await database
            .execute('insert into Demo(name, age) values("jack", 18)');

        database.close();
        Fluttertoast.showToast(msg: "添加数据完成");
      },
      '删除数据': () async {
        var database = await _database();
        await database.execute('delete from Demo');

        database.close();
        Fluttertoast.showToast(msg: "删除数据完成");
      },
      '查询数据': () async {
        var database = await _database();
        var datas = await database.rawQuery('select * from Demo');
        print(datas);

        database.close();
        Fluttertoast.showToast(msg: "查询数据完成:$datas");
      },
      '': () {},
      'SQL helpers': () async {
        // 这里以查询做示例，其他操作还有，delete,insert,update 等
        var database = await _database();
        var datas = await database.query('Demo', limit: 1);

        database.close();
        Fluttertoast.showToast(msg: "helpers查询数据完成:$datas");
      },
      'Transaction(事物)': () async {
        var database = await _database();

        await database.transaction((tran) async {
          for (int i = 0; i < 3; i++) {
            tran.insert('Demo', {'name': 'Jack$i', 'age': 14 + i});
          }
        });

        database.close();
        Fluttertoast.showToast(msg: "Transaction插入数据完成");
      },
      'Batch support (批处理)': () async {
        var database = await _database();
        var batch = database.batch();

        batch.insert('Demo', {'name': 'Lily', 'age': 11});
        batch.insert('Demo', {'name': 'Qiong Senq', 'age': 7});
        await batch.commit();

        database.close();
        Fluttertoast.showToast(msg: "批处理插入数据完成");
      },
      ' ': () {},
      'doc: https://pub.flutter-io.cn/packages/sqflite#-readme-tab-': () =>
          pushMaterial(
              context,
              WebViewPage(
                title: 'doc',
                url: 'https://pub.flutter-io.cn/packages/sqflite#-readme-tab-',
              )),
    };

    return Scaffold(
      appBar: AppBar(
        title: Text('SqlLiteDemo'),
      ),
      body: ListView.separated(
        itemBuilder: (context, position) {
          var item = items.entries.elementAt(position);
          return ListTile(
            title: Text(item.key),
            onTap: item.value,
            trailing: Icon(Icons.keyboard_arrow_right),
          );
        },
        itemCount: items.length,
        separatorBuilder: (context, position) => Divider(
              height: 1,
            ),
      ),
    );
  }
}
